from sqlalchemy import create_engine, MetaData, LargeBinary
from sqlalchemy import Integer, String, ForeignKey, Column, TEXT
from sqlalchemy.orm import relationship
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
from sqlalchemy import PrimaryKeyConstraint, UniqueConstraint
import sqlalchemy

# 使用ORM改造原有类

Base = declarative_base()


# 新的Store类，调整为拥有与数据库使用ORM连接的能力

class Store_:
    def connect():
        url = 'postgresql://stu10205501440:Stu10205501440@dase-cdms-2022-pub.pg.rds.aliyuncs.com:5432/bookstore'
        con = create_engine(url, client_encoding='utf8')
        meta = MetaData(bind=con)
        return con, meta


class User(Base):
    __tablename__ = 'user'
    user_id = Column(TEXT, primary_key=True, comment="用户id")
    password = Column(TEXT, nullable=False, comment="密码")
    balance = Column(Integer, nullable=False, comment="余额")
    token = Column(TEXT, comment="令牌")
    terminal = Column(TEXT, comment="终端代码")


class Store(Base):
    __tablename__ = 'store'
    store_id = Column(TEXT, primary_key=True, comment="商店ID")
    stock_level = Column(Integer, comment="库存")


class Store_Book(Base):
    __tablename__ = 'store_book'
    store_id = Column(TEXT, comment="商店ID")
    book_id = Column(TEXT, comment="书本ID")
    book_info = Column(TEXT, comment="书籍信息")
    stock_level = Column(Integer, comment="库存")

    __table_args__ = (
        PrimaryKeyConstraint('store_id', 'book_id'),
    )


class User_Store(Base):
    __tablename__ = 'user_store'

    id = Column(Integer, primary_key=True, autoincrement=True, comment="主键")

    fk_user_id = Column(
        TEXT,
        ForeignKey(
            "user.user_id",
            ondelete="CASCADE",
            onupdate="CASCADE",
        ),
        nullable=False,
        comment="user外键"
    )
    fk_store_id = Column(
        TEXT,
        ForeignKey(
            "store.store_id",
            ondelete="CASCADE",
            onupdate="CASCADE",
        ),
        nullable=False,
        comment="store外键"
    )
    # 多对多关系的中间表必须使用联合唯一约束，防止出现重复数据
    __table_args__ = (
        UniqueConstraint("fk_user_id", "fk_store_id"),
    )


class New_Order(Base):
    __tablename__ = 'new_order'
    order_id = Column(TEXT, primary_key=True, comment='订单id')

    fk_user_id = Column(
        TEXT,
        ForeignKey(
            "user.user_id",
            ondelete="CASCADE",
            onupdate="CASCADE",
        ),
        nullable=False,
        comment="user外键"
    )
    fk_store_id = Column(
        TEXT,
        ForeignKey(
            "store.store_id",
            ondelete="CASCADE",
            onupdate="CASCADE",
        ),
        nullable=False,
        comment="store外键"
    )
    create_time = Column(TEXT, nullable=False, comment="订单创建时间")
    status = Column(Integer, nullable=False, comment="订单状态")


class New_Order_Detail(Base):
    __tablename__ = 'new_order_detail'
    order_id = Column(TEXT, comment='订单id')
    book_id = Column(TEXT, comment='订单书籍')
    count = Column(Integer, comment='购买数量')
    price = Column(Integer, comment='单价')

    __table_args__ = (
        PrimaryKeyConstraint('order_id', 'book_id'),
    )

class Book(Base):
    __tablename__ = 'book'
    book_id = Column(TEXT, primary_key=True)
    title = Column(TEXT, nullable=False)
    store_id = Column(TEXT, comment="商店ID")
    author = Column(TEXT)
    publisher = Column(TEXT)
    original_title = Column(TEXT)
    translator = Column(TEXT)
    pub_year = Column(TEXT)
    pages = Column(Integer)
    original_price = Column(Integer)
    currency_unit = Column(TEXT)
    binding = Column(TEXT)
    isbn = Column(TEXT)
    author_intro = Column(TEXT)
    book_intro = Column(TEXT)
    content = Column(TEXT)
    tags = Column(TEXT)
    picture = Column(LargeBinary)

class invert_index(Base):
    __tablename__= "invert_index"
    search_key=Column(TEXT)
    search_id=Column(Integer,autoincrement=True)
    book_title=Column(TEXT)
    book_author=Column(TEXT)
    book_intro=Column(TEXT)
    store_id = Column(TEXT, comment="商店ID")
    __table_args__ = (
        PrimaryKeyConstraint('search_key', 'search_id'),
    )


'''
"CREATE TABLE IF NOT EXISTS invert_index( "
                "search_key TEXT, search_id serial, book_id TEXT, "
                "book_title TEXT, book_author TEXT, "
                "PRIMARY KEY(search_key, search_id))"


'''




engine, meta = Store_.connect()
Base.metadata.bind = engine
DBSession = sessionmaker(bind=engine)
session = DBSession()


def init_database():
    Base.metadata.create_all(engine)
